CREATE    PROCEDURE amsp_ICFixTree
    @l_ParentCategoryID   Numeric=0 ,
    @l_SortOrder   Numeric(28,18) = 0 OUT,
    @l_CategoryDepth  Numeric=0,
    @l_AncestorID Numeric=0
AS

  /*
  ** DESCRIPTION:
  ** ------------
  ** Walks the tree and sets the CategoryDepth, SortOrder, AncestorID, AncestorOrder based on the
  ** ParentID relationship.
  **
  ** INPUTS:
  ** -------
  **   none
  **
  ** OUTPUTS:
  ** -----------
  **   none
  **
  ** NOTES:
  ** ------
  **   Recursive so only supports 255 levels in the tree... should NOT be a problem.
  **
  ** HISTORY:
  ** --------
  **   04/23/2003    N.Malhotra    Initial Version Created
  **
  */

DECLARE @b CURSOR

SET @l_CategoryDepth=@l_CategoryDepth+1

IF @l_ParentCategoryID= 0
  BEGIN
    SET @b=CURSOR STATIC FOR
    SELECT InterestCategoryID
      FROM Interest_Category
     WHERE ParentCategoryID IS NULL
     ORDER BY SortOrder
  END
ELSE
  BEGIN
    SET @b=CURSOR STATIC FOR
    SELECT InterestCategoryID
      FROM Interest_Category
     WHERE ParentCategoryID=@l_ParentCategoryID
     ORDER BY SortOrder
END

DECLARE @l_IntrestID Numeric

OPEN @b
FETCH FROM @b INTO @l_IntrestID
IF NOT @@FETCH_STATUS=0 RETURN
  WHILE @@FETCH_STATUS=0
    BEGIN
      IF @l_ParentCategoryID= 0
        SET @l_AncestorID=@l_IntrestID    

      SET @l_SortOrder=@l_SortOrder+1000
      SET @l_IntrestID = CAST(@l_IntrestID AS Numeric)

      UPDATE Interest_Category
         SET SortOrder=@l_SortOrder,
             CategoryDepth=@l_CategoryDepth,
             AncestorCategoryID=@l_AncestorID,
             AncestorOrder=@l_SortOrder
      WHERE InterestCategoryID=@l_IntrestID

      EXEC amsp_ICFixTree @l_IntrestID, @l_SortOrder OUT, @l_CategoryDepth, @l_AncestorID

      FETCH FROM @b INTO @l_IntrestID             
    END

GO
GRANT EXECUTE ON  [dbo].[amsp_ICFixTree] TO [IMIS]
GO